package com.cn.wx.dao.impl;

import com.cn.wx.dao.LoginDao;
import com.cn.wx.model.UserModel;
import com.cn.wx.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库实现层
 * 编写sql语句，执行数据库操作
 */
public class LoginImpl implements LoginDao {
    @Override
    public UserModel getUserByName(String username) {

        String sql = "select * from user where username='" + username + "'";
        ResultSet resultSet = JDBC.getResultSet(sql);
        UserModel userModel = null;
        try {
            while (resultSet.next()) {
                userModel = new UserModel();
                userModel.setId(resultSet.getInt("id"));
                userModel.setUsername(resultSet.getString("username"));
                userModel.setPassword(resultSet.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }

        return userModel;
    }

    @Override
    public List<UserModel> getAll(Integer pageSize, Integer pageNum) {
        String sql = "select * from user limit " + (pageNum-1) * pageSize + "," + pageSize;
        List<UserModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql);

        try {
            while (resultSet.next()) {
                UserModel userModel = new UserModel();
                userModel.setId(resultSet.getInt("id"));
                userModel.setUsername(resultSet.getString("username"));
                userModel.setPassword(resultSet.getString("password"));
                list.add(userModel);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }


        return list;
    }

    @Override
    public UserModel getUserById(Integer id) {
        String sql = "select * from user where id=" + id;
        ResultSet resultSet = JDBC.getResultSet(sql);
        UserModel userModel = null;
        try {
            while (resultSet.next()) {
                userModel = new UserModel();
                userModel.setId(resultSet.getInt("id"));
                userModel.setUsername(resultSet.getString("username"));
                userModel.setPassword(resultSet.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }

        return userModel;
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "update user set username='" + userModel.getUsername() + "', password='" + userModel.getPassword() + "' where id=" + userModel.getId();
        return JDBC.excuteUpdate(sql);
    }

    @Override
    public int delete(Integer id) {
        String sql = "delete from user where id=" + id;
        return JDBC.excuteUpdate(sql);
    }

    @Override
    public int add(UserModel userModel) {
        String sql = "insert into user(username,password)values('" + userModel.getUsername() + "','" + userModel.getPassword() + "')";
        return JDBC.excuteUpdate(sql);
    }

    @Override
    public int count(String string) {
        //得到总条数，以后我们学了数据优化的时候，就不能写*
//        select count(id) from user,count里面不能写列有空的
//        所以这里可以用id，
        String sql = "select count(*) as count  from user";
        ResultSet resultSet = JDBC.getResultSet(sql);
        String count = "-1";
        try {
            while (resultSet.next()) {
                count = resultSet.getString("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }
        return Integer.parseInt(count);
    }

}
